package com.ky.pro.java.fx.jdbc;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @ClassName JdbcMain
 * @Description 优化后的JDBC实现
 * @Date 2021/12/4 21:15
 * @Author kevin
 **/
public class JdbcMain {

    public static void main(String[] args) throws SQLException {

        Properties properties = null;
        try (InputStream is = JdbcMain.class.getClassLoader().getResourceAsStream("hikari.properties")) {
            properties = new Properties();
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }

        if (properties != null) {
            HikariConfig hikariConfig = new HikariConfig(properties);
            HikariDataSource dataSource = new HikariDataSource(hikariConfig);

            Connection connection = null;
            Statement statement = null;

            try {

                connection = dataSource.getConnection();
                statement = connection.createStatement();

                DataUtil.initDB(statement);

                PreparedStatement psSchool = connection.prepareStatement("insert into t_school values(?, ?)");
                PreparedStatement psKlass = connection.prepareStatement("insert into t_klass values(?, ?, ?)");
                PreparedStatement psStudent = connection.prepareStatement("insert into t_student values(?, ?, ?, ?, ?)");

                connection.setAutoCommit(false);

                // 批量创建学校、班级、学生
                for (int i = 1; i <= 3; i++) {
                    int schoolId = i;
                    psSchool.setInt(1, schoolId);
                    psSchool.setString(2, "No." + i + " High School");
                    psSchool.addBatch();
                    for (int j = 1; j <= 5; j++) {
                        int klassId = j + i * 10;
                        psKlass.setInt(1, klassId);
                        psKlass.setString(2, i + "-" + j);
                        psKlass.setInt(3, schoolId);
                        psKlass.addBatch();
                        for (int k = 1; k <= 5; k++) {
                            psStudent.setInt(1, k + j * 10 + i * 100);
                            psStudent.setString(2, "student-" + i + j + k);
                            psStudent.setInt(3, 16);
                            psStudent.setInt(4, k % 2);
                            psStudent.setInt(5, klassId);
                            psStudent.addBatch();
                        }
                    }
                }
                psSchool.executeBatch();
                psKlass.executeBatch();
                psStudent.executeBatch();

                // 查询所有学生详情
                ResultSet rs = statement.executeQuery("select * from t_student st join" +
                        "(select s.id as school_id, s.name as school_name, " +
                        "k.id as klass_id, k.name as klass_name " +
                        "from t_school s join t_klass k on k.school_id=s.id) sk " +
                        "on st.klass_id = sk.klass_id");

                connection.commit();

                // 显示查询结果
                DataUtil.dumpQueryResult(rs);

            } catch (SQLException e) {
                e.printStackTrace();
                if (connection != null) {
                    connection.rollback();
                }
            } finally {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.setAutoCommit(true);
                    connection.close();
                }
            }
        }

    }

}
